<?xml version="1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>Twisted Enterprise Row Objects</title>
</head>
<body>

<div class="note">
<p>
Due to lack of maintenance, <code>twisted.enterprise.row</code>
and <code>twisted.enterprise.reflector</code> have been deprecated since
Twisted 8.0.
</p>

<p>
This documentation is maintained only for users with an existing
codebase.
</p>
</div>


<h1>Twisted Enterprise Row Objects</h1>

<p>The <code>twisted.enterprise.row</code> module is a method of
interfacing simple python objects with rows in relational database
tables. It has two components: the <code>RowObject</code> class which
developers sub-class for each relational table that their code
interacts with, and the <code>Reflector</code> which is responsible
for updates, inserts, queries and deletes against the database.</p>

<p>The row module is intended for applications such as on-line
games, and websites that require a back-end database interface.
It is not a full functioned object-relational mapper for python
- it deals best with simple data types structured in ways that
can be easily represented in a relational database. It is well
suited to building a python interface to an existing relational
database, and slightly less suited to added database persistance
to an existing python application.</p>

<p><em>If row does not fit your model, you will be best off using
the <a href="enterprise.xhtml">low-level database API</a> directly, 
or writing your own object/relational layer on top of it.</em></p>

<h2>Class Definitions</h2>

<p>To interface to relational database tables, the developer must
create a class derived from the <code>twisted.enterprise.row.RowObject</code>
class for each table. These derived classes must define a number
of class attributes which contains information about the database
table that class corresponds to. The required class attributes
are:</p>

<ul>
  <li>rowColumns - list of the column names and types in the table with
  the correct case</li>
  <li>rowKeyColumns - list of key columns in form: <code>[(columnName,
  typeName)]</code></li>
  <li>rowTableName - the name of the database table</li>
</ul>

<p>There are also two optional class attributes that can be specified:</p>

<ul>
  <li>rowForeignKeys - list of foreign keys to other database tables
   in the form: <code>[(tableName, [(childColumnName, childColumnType), ...], 
   [(parentColumnName, parentColumnType), ...], containerMethodName, autoLoad]</code></li>
  <li>rowFactoryMethod - a method that creates instances of this
  class</li>
</ul>

<p>For example:</p>

<pre class="python">
class RoomRow(row.RowObject):
    rowColumns       = [("roomId",  "int"), 
                        ("town_id", "int"),
                        ("name",    "varchar"),
                        ("owner",   "varchar"),
                        ("posx",    "int"),
                        ("posy",    "int"),
                        ("width",   "int"),
                        ("height",  "int")]
    rowKeyColumns    = [("roomId",  "int4")]
    rowTableName     = "testrooms"
    rowFactoryMethod = [testRoomFactory]
</pre>

<p>The items in the rowColumns list will become data members of
classes of this type when they are created by the Reflector.</p>

<h2>Initialization</h2>

<p>The initialization phase builds the SQL for the database interactions.
It uses the system catalogs of the database to do this, but requires
some basic information to get started. The class attributes of
the classes derived from RowClass are used for this. Those classes
are passed to a Reflector when it is created.</p>

<p>There are currently two available reflectors in Twisted Enterprise,
the SQL Reflector for relational databases which uses the python DB
API, and the XML Reflector which uses a file system containing XML
files. The XML reflector is currently extremely slow.</p>

<p>An example class list for the RoomRow class we specified above using the SQLReflector:</p>

<pre class="python">

from twisted.enterprise.sqlreflector import SQLReflector

dbpool = adbapi.ConnectionPool("pyPgSQL.PgSQL")
reflector = SQLReflector( dbpool, [RoomRow] )
</pre>

<h2>Creating Row Objects</h2>

<p>There are two methods of creating RowObjects - loading from
the database, and creating a new instance ready to be inserted.</p>

<p>To load rows from the database and create RowObject instances
for each of the rows, use the loadObjectsFrom method of the Reflector.
This takes a tableName, an optional <q>user data</q> parameter,
and an optional <q>where clause</q>. The where clause may
be omitted which will retrieve all the rows from the table. For
example:</p>

<pre class="python">
def gotRooms(rooms):
    for room in rooms:
        print "Got room:", room.id

d = reflector.loadObjectsFrom("testrooms", 
                              whereClause=[("id", reflector.EQUAL, 5)])
d.addCallback(gotRooms)
</pre>

<p>For more advanced RowObject construction, loadObjectsFrom may
use a factoryMethod that was specified as a class attribute for
the RowClass derived class. This method will be called for each
of the rows with the class object, the userData parameter, and
a dictionary of data from the database keyed by column name. This
factory method should return a fully populated RowObject instance
and may be used to do pre-processing, lookups, and data transformations
before exposing the data to user code. An example factory method:</p>

<pre class="python">
def testRoomFactory(roomClass, userData, kw):
    newRoom = roomClass(userData)
    newRoom.__dict__.update(kw)
    return newRoom</pre>

<p>The last method of creating a row object is for new instances
that do not already exist in the database table. In this case,
create a new instance and assign its primary key attributes and
all of its member data attributes, then pass it to the <code>insertRow</code>
method of the Reflector. For example:</p>

<pre class="python">
    newRoom = RoomRow()
    newRoom.assignKeyAttr("roomI", 11)
    newRoom.town_id = 20
    newRoom.name = 'newRoom1'
    newRoom.owner = 'fred'
    newRoom.posx = 100
    newRoom.posy = 100
    newRoom.width = 15
    newRoom.height = 20
    reflector.insertRow(newRoom).addCallback(onInsert)
</pre>

<p>This will insert a new row into the database table for this
new RowObject instance. Note that the <code>assignKeyAttr</code>
method must be used to set primary key attributes - regular attribute
assignment of a primary key attribute of a rowObject will raise
an exception. This prevents the database identity of RowObject
from being changed by mistake.</p>


<h2>Relationships Between Tables</h2>

<p>Specifying a foreign key for a RowClass creates a relationship
between database tables. When <code
class="python">loadObjectsFrom</code> is called for a table, it will
automatically load all the children rows for the rows from the specified
table. The child rows will be put into a list member variable of the
rowObject instance with the name <code>childRows</code> or if a
<em>containerMethod</em> is specified for the foreign key relationship,
that method will be called on the parent row object for each row that is
being added to it as a child.</p>

<p>The <em>autoLoad</em> member of the foreign key definition is a flag
that specifies whether child rows should be auto-loaded for that
relationship when a parent row is loaded.</p>

<h2>Duplicate Row Objects</h2>

<p>If a reflector tries to load an instance of a rowObject that
is already loaded, it will return a reference to the existing
rowObject rather than creating a new instance. The reflector maintains
a cache of weak references to all loaded row objects by their
unique keys for this purpose.</p>

<h2>Updating Row Objects</h2>

<p>RowObjects have a <code>dirty</code> member attribute that is
set to 1 when any of the member attributes of the instance that
map to database columns are changed. This dirty flag can be used
to tell when RowObjects need to be updated back to the database.
In addition, the <code>setDirty</code> method can be overridden
to provide more complex automated handling such as dirty lists
(be sure to call the base class setDirty though!).</p>

<p>When it is determined that a RowObject instance is dirty and
need to have its state updated into the database, pass that object
to the <code>updateRow</code> method of the Reflector. For example:</p>

<pre class="python">
reflector.updateRow(room).addCallback(onUpdated)
</pre>

<p>For more complex behavior, the reflector can generate the SQL
for the update but not perform the update. This can be useful
for batching up multiple updates into single requests. For example:</p>

<pre class="python">
updateSQL = reflector.updateRowSQL(room)
</pre>

<h2>Deleting Row Objects</h2>

<p>To delete a row from a database pass the RowObject instance
for that row to the Reflector <code>deleteRow</code> method.
Deleting the python Rowobject instance does <em>not</em> automatically
delete the row from the database. For example:</p>

<pre class="python">
reflector.deleteRow(room)
</pre>

</body>
</html>
